#!/usr/bin/python3
# -*- coding:utf-8 -*-
# yn-2364511934@qq.com

from urllib.request import urlopen
from urllib.request import Request
from bs4 import  BeautifulSoup
from time import sleep
import pymysql
import re
import datetime

base_movie_url="https://movie.douban.com/top250?start="

"""
CREATE TABLE `douban_movie_top250` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `mtitle` varchar(255) NOT NULL COMMENT '主标题',
  `stitle` varchar(255) DEFAULT NULL COMMENT '副标题',
  `otitle` varchar(255) DEFAULT NULL COMMENT '其他标题',
  `director` varchar(255) DEFAULT NULL COMMENT '导演 ',
  `starring` varchar(255) DEFAULT NULL COMMENT '主演',
  `myear` varchar(255) DEFAULT NULL COMMENT '上映年份',
  `nation` varchar(255) DEFAULT NULL COMMENT '国别',
  `mtype` varchar(255) DEFAULT NULL COMMENT '电影类型',
  `score` double DEFAULT NULL COMMENT '电影评分',
  `comment` int(11) DEFAULT NULL COMMENT '好评数',
  `quote` varchar(255) DEFAULT NULL COMMENT '引用',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
"""

con = pymysql.connect(host="hadoop01", user="root", password="root", database="spider", charset='utf8', port=3306)
cursor = con.cursor()
movie_insert_sql = "insert into douban_movie_top250 " \
                   "(mtitle, stitle, otitle, director, starring, myear, nation, mtype, score, comment, quote)" \
                   " VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)"


def get_movie(link):
    url_response = urlopen(link)
    bs = BeautifulSoup(url_response, "html.parser")
    #电影主题
    titles_raw_list= bs.select("div.hd a")
    rel_title_main_list = []
    rel_title_sub_list=[]
    rel_title_other_list=[]
    for title in titles_raw_list:
        rel_title_main = "主标题"
        rel_title_sub = ""
        rel_title_other = ""
        rel_title_list = title.select("span.title")
        if 1 == len(rel_title_list):
            rel_title_main = rel_title_list[0].text
            #print(rel_title_main)
        if 2 == len(rel_title_list):
            rel_title_main = rel_title_list[0].text
            rel_title_sub = rel_title_list[1].text.strip().strip('/').strip()
        rel_title_main_list.append(rel_title_main)
        rel_title_sub_list.append(rel_title_sub)
        #print(rel_title_main)
        #print(rel_title_sub)

        rel_other_list = title.select("span.other")
        if 0 != len(rel_other_list):
            rel_other_list2 = rel_other_list[0].text.strip().strip('/').split('/')
            rel_other_list = []
            for other in rel_other_list2:
                rel_other_list.append(other.strip())
            rel_other = ",".join(rel_other_list)
        rel_title_other_list.append(rel_other)
        #print(rel_other)
        #print(title)

    #导演 主演 上映年份 国别 电影类型
    rel_director_list=[]
    rel_starring_list=[]
    rel_year_list=[]
    rel_nation_list=[]
    rel_movie_type_list=[]
    content_raw_list = bs.select("div.bd p.")
    i = 0
    for content in content_raw_list:
        content_array_raw= content.text.strip('\n').split("  ")
        content_array = []
        for content in content_array_raw:
            if 0 != len(content):
                content_array.append(content.strip().strip('\n'))
        direct_starring_raw = content_array[0].strip().strip('\n').split("   ")
        direct = "导演:"
        starring="主演:"
        if 1 == len(direct_starring_raw):
            direct=direct_starring_raw[0]
        if 2 == len(direct_starring_raw):
            direct = direct_starring_raw[0]
            starring = direct_starring_raw[1]
        direct = direct.strip().replace("/", "|")
        starring = starring.strip().replace("/", "|")
        rel_director_list.append(direct)
        rel_starring_list.append(starring)
        #print(direct)
        #print(starring)
        year_nation_movie_type = content_array[1].strip().split('/')
        year = "0000"
        nation="未知国家"
        movie_type="未知电影类型"
        if 1 == len(year_nation_movie_type):
            year = year_nation_movie_type[0]
        if 2 == len(year_nation_movie_type):
            year = year_nation_movie_type[0]
            nation = year_nation_movie_type[1]
        if 3 == len(year_nation_movie_type):
            year = year_nation_movie_type[0]
            nation = year_nation_movie_type[1]
            movie_type = year_nation_movie_type[2]
        year = year.strip()
        nation = nation.strip().replace(" ", "|")
        movie_type = movie_type.strip().replace(" ", "|")
        rel_year_list.append(year)
        rel_nation_list.append(nation)
        rel_movie_type_list.append(movie_type)
        #print(year)
        #print(nation)
        #print(movie_type)
        #print(content_array[1].strip())
        #print(",".join(content_array))

    #电影评分和评论
    rel_score_list = []
    rel_comment_list =[]
    score_raw_list = bs.select("div.star span.rating_num")
    for score in score_raw_list:
        rel_score="0"
        rel_score = score.text
        #print(rel_score)
        rel_score_list.append(rel_score)

    comment_raw_list = bs.select("div.star")
    for comment in comment_raw_list:
        rel_comment = "0"
        comment_list = comment.select("span")
        if 4 == len(comment_list):
            rel_comment = comment_list[3].text.strip("人评价")
        #print(rel_comment)
        rel_comment_list.append(rel_comment)


    #引用
    rel_quote_list = []
    quote_raw_list = bs.select("p.quote span.inq")
    i=0
    for quote in quote_raw_list:
        if 11 == i and  24 == len(quote_raw_list):
            rel_quote_list.append("未知引用")
            print("未知引用")
        rel_quote = quote.text.strip("。")
        #print(rel_quote)
        rel_quote_list.append(rel_quote)
        i +=1

    # print(len(rel_title_main_list))
    # print(len(rel_title_sub_list))
    # print(len(rel_title_other_list))
    # print(len(rel_director_list))
    # print(len(rel_starring_list))
    # print(len(rel_year_list))
    # print(len(rel_nation_list))
    # print(len(rel_movie_type_list))
    # print(len(rel_score_list))
    # print(len(rel_comment_list))
    # print(len(rel_quote_list))
    for i in range(len(rel_title_main_list)):
        #print(",".join([rel_title_main_list[i],rel_title_sub_list[i],rel_title_other_list[i],rel_director_list[i],
        #rel_starring_list[i],rel_year_list[i],rel_nation_list[i], rel_movie_type_list[i],rel_score_list[i],
        #rel_comment_list[i],rel_quote_list[i]]))


        record = [rel_title_main_list[i],rel_title_sub_list[i],rel_title_other_list[i],rel_director_list[i],
        rel_starring_list[i],rel_year_list[i],rel_nation_list[i], rel_movie_type_list[i],rel_score_list[i],
        rel_comment_list[i],rel_quote_list[i]]

        cursor.execute(movie_insert_sql, record)
        con.commit()

def main():
    posLink = 25
    for i in range(10):
        movie_url = base_movie_url + str(posLink*(i+1))
        print("正在爬取:", movie_url)
        get_movie(movie_url)
        sleep(5)
main()